package com.opennews.openplatform.familyexpenseapi.service

import com.opennews.openplatform.familyexpenseapi.config.AppConfigProperties
import com.opennews.openplatform.familyexpenseapi.dto.user.UpdateUserDto
import com.opennews.openplatform.familyexpenseapi.dto.user.UpdateUserRolesDto
import com.opennews.openplatform.familyexpenseapi.entity.Tables.ACCOUNT_GROUP_USER
import com.opennews.openplatform.familyexpenseapi.entity.Tables.USER
import com.opennews.openplatform.familyexpenseapi.jooq.DSLContextWrapper
import com.opennews.openplatform.myspringbootcore.common.constant.ROLE_ACCOUNT_GROUP_ADMIN
import com.opennews.openplatform.myspringbootcore.extension.isNullOrBlankOrLiteralNull
import jakarta.validation.Valid
import org.jooq.DSLContext
import org.jooq.impl.DSL
import org.springframework.core.env.Environment
import org.springframework.security.crypto.factory.PasswordEncoderFactories
import org.springframework.stereotype.Service
import java.io.File
import java.nio.file.Paths

@Service
class UserService(
    private val appConfigProperties: AppConfigProperties,
    private val dslContext: DSLContext,
    private val dslContextWrapper: DSLContextWrapper,
    private val env: Environment,
    private val userActiveTokenService: UserActiveTokenService,
    private val userRoleService: UserRoleService,
) {
    /**
     * Masks username and avoids sensitive data revealing.
     */
    fun maskUsername(username: String): String {
        return "${username.substring(0, 3)}****${username.substring(username.length - 4)}"
    }

    /**
     * Validates if the presentedPassword matches the user stored password.
     *
     * @param username:          The string of the username.
     * @param presentedPassword: The string of the user provided password.
     * @return True means password matches. False means it does not.
     */
    fun validatePassword(username: String, presentedPassword: String): Boolean {
        val encodedPassword = dslContext.selectFrom(USER)
            .where(USER.USERNAME.eq(username))
            .fetchOne(USER.PASSWORD)

        if (!encodedPassword.isNullOrBlankOrLiteralNull()) {
            return PasswordEncoderFactories.createDelegatingPasswordEncoder()
                .matches(presentedPassword, encodedPassword)
        }

        return false
    }

    /**
     * Queries the user status by username.
     *
     * @param username: The string of the username.
     * @return A map containing user details such as id, username, enabled status, account expired status, account locked status, and password expired status.
     */
    fun queryStatus(username: String): Map<String, Any>? {
        return dslContext
            .select(
                USER.ID.`as`("id"),
                USER.USERNAME.`as`("username"),
                USER.ENABLED.`as`("enabled"),
                USER.ACCOUNT_EXPIRED.`as`("accountExpired"),
                USER.ACCOUNT_LOCKED.`as`("accountLocked"),
                USER.PASSWORD_EXPIRED.`as`("passwordExpired"),
            )
            .from(USER)
            .where(USER.USERNAME.eq(username))
            .fetchOneMap()
    }

    /**
     * Checks if the username already exists.
     *
     * @param username: The username which will be checked for existence.
     * @return True means the username exists. False means it does not.
     */
    fun checkOnUsername(username: String): Boolean {
        return dslContextWrapper.exists(USER, USER.USERNAME.eq(username))
    }

    /**
     * Gets user avatar URL based on its filename.
     *
     * @param avatarFilename The avatar filename of the uploaded file.
     * @return The full URL of the user avatar.
     */
    fun getAvatarUrl(avatarFilename: String?): String {
        val avatarFilename = avatarFilename ?: ""

        val avatarFileFullName = Paths.get(
            appConfigProperties.resource.upload.rootFilePath,
            appConfigProperties.resource.avatarDirectoryName,
            avatarFilename
        ).toString()

        if (avatarFilename.isEmpty() || !File(avatarFileFullName).exists()) {
            return appConfigProperties.resource.defaultAvatarUrl!!
        }

        return "%s/%s/%s".format(
            appConfigProperties.resource.httpBaseUrl,
            appConfigProperties.resource.avatarDirectoryName,
            avatarFilename
        )
    }

    /**
     * Queries the user details by id. This is for query account group user details.
     *
     * @param accountGroupId: The string of user account group id.
     * @param idOrUsername:   The string of id or username.
     * @return The map instance which contains keys below.
     * accountGroupId: String of user account group.
     * id: String of user id.
     * username: String of username.
     * fullName: String of user full name.
     * avatarUrl: String of user avatar url.
     * description: String of user description.
     * isAccountGroupCreator: Bool indicates if the user is account group registerer.
     * enabled: Bool indicates if the user is enabled or disabled.
     * fullRoles: Data returned by userRoleService.queryRoleWithIdAndTitles().
     * dateCreated: Date of the data created.
     * lastUpdated: Date of the data updated.
     */
    fun queryDetails(accountGroupId: String, idOrUsername: String): Map<String, Any?>? {
        // Query user details from the database
        val userDetails = dslContext
            .select(
                ACCOUNT_GROUP_USER.ACCOUNT_GROUP_ID.`as`("accountGroupId"),
                USER.ID.`as`("id"),
                USER.USERNAME.`as`("username"),
                USER.FULL_NAME.`as`("fullName"),
                USER.AVATAR_FILENAME.`as`("avatarFilename"),
                USER.DESCRIPTION.`as`("description"),
                USER.ENABLED.`as`("enabled"),
                USER.DATE_CREATED.`as`("dateCreated"),
                USER.LAST_UPDATED.`as`("lastUpdated"),
            )
            .from(ACCOUNT_GROUP_USER)
            .join(USER).on(ACCOUNT_GROUP_USER.USER_ID.eq(USER.ID))
            .where(
                ACCOUNT_GROUP_USER.ACCOUNT_GROUP_ID.eq(accountGroupId)
                    .and(
                        DSL.or(
                            USER.ID.eq(idOrUsername),
                            USER.USERNAME.eq(idOrUsername)
                        )
                    )
            )
            .fetchOneMap()  // Fetch a single result map

        // If no user details are found, return an empty map or handle accordingly
        if (userDetails == null) {
            return emptyMap()
        }

        // Fulfill the details with any additional logic if needed
        return fulfillDetails(accountGroupId, userDetails)
    }

    /**
     * Queries the user details by id. It is for authentication usage.
     *
     * @param idOrUsername: The string of id or username.
     * @return The map instance which contains keys below.
     * accountGroupId: String of user account group.
     * id: String of user id.
     * username: String of username.
     * fullName: String of user full name.
     * avatarUrl: String of user avatar url.
     * description: String of user description.
     * enabled: Bool indicates if the user is enabled or disabled.
     * fullRoles: Data returned by userRoleService.queryRoleWithIdAndTitles().
     * dateCreated: Date of the data created.
     * lastUpdated: Date of the data updated.
     */
    fun queryDetails(idOrUsername: String): Map<String, Any?>? {
        val userDetails = dslContext
            .select(
                USER.ACCOUNT_GROUP_ID.`as`("accountGroupId"),
                USER.ID.`as`("id"),
                USER.USERNAME.`as`("username"),
                USER.FULL_NAME.`as`("fullName"),
                USER.AVATAR_FILENAME.`as`("avatarFilename"),
                USER.DESCRIPTION.`as`("description"),
                USER.ENABLED.`as`("enabled"),
                USER.DATE_CREATED.`as`("dateCreated"),
                USER.LAST_UPDATED.`as`("lastUpdated"),
            )
            .from(USER)
            .where(
                DSL.or(
                    USER.ID.eq(idOrUsername),
                    USER.USERNAME.eq(idOrUsername)
                )
            )
            .fetchOneMap()

        return fulfillDetails(null, userDetails)
    }

    /**
     * Queries the user ids by usernames.
     *
     * @param usernames: The string list of the usernames to query ids.
     * @return The string list of the user ids.
     */
    fun queryUserIds(usernames: List<String>): List<String> {
        return if (usernames.isNotEmpty()) {
            dslContext.select(USER.ID)
                .from(USER)
                .where(USER.USERNAME.`in`(usernames))
                .fetchInto(String::class.java)
        } else {
            emptyList()
        }
    }

    /**
     * Queries the user id by username.
     *
     * @param username: The string of the username to query its id.
     * @return The string of the user id or null if not found.
     */
    fun queryUserId(username: String): String? {
        val result = queryUserIds(listOf(username))
        return result?.firstOrNull()
    }

    /**
     * Tries to query user basic info. The original value is returned if user not found.
     *
     * @param accountGroupId: String of user account group id.
     * @param usernames:      List of usernames.
     * @return A list of maps containing user basic information.
     */
    fun queryBasicInfo(accountGroupId: String, usernames: List<String>?): List<Map<String, Any>> {
        val uniqueUsernames = usernames?.toHashSet() ?: hashSetOf()
        val result = mutableListOf<Map<String, Any>>()

        if (uniqueUsernames.isNotEmpty()) {
            val userDetails = dslContext
                .select(
                    USER.ID.`as`("id"),
                    USER.USERNAME.`as`("username"),
                    USER.FULL_NAME.`as`("fullName"),
                    USER.AVATAR_FILENAME.`as`("avatarFilename"),
                )
                .from(USER)
                .where(
                    USER.USERNAME.`in`(uniqueUsernames)
                        .and(USER.ACCOUNT_GROUP_ID.eq(accountGroupId))
                )
                .fetchMaps()

            for (username in uniqueUsernames) {
                val item = userDetails.firstOrNull { it["username"] == username }

                if (item != null) {
                    result.add(
                        mapOf(
                            "username" to item["username"] as String,
                            "fullName" to item["fullName"] as String,
                            "avatarUrl" to getAvatarUrl(item["avatarFilename"] as String?)
                        )
                    )
                } else {
                    result.add(
                        mapOf(
                            "username" to username,
                            "fullName" to username,
                            "avatarUrl" to getAvatarUrl("")
                        )
                    )
                }
            }
        }

        return result
    }

    /**
     * Queries users who are not in the provided account group.
     *
     * @param accountGroupId: String of user account group id.
     * @param keyword:        The string of the user account group id.
     * @param pageSize:       How many records are returned in one trip.
     * @param pageIndex:      Where to take rows.
     * @return The list of maps, each containing the following keys:
     * id: String of user id.
     * username: String of username.
     * fullName: String of user full name.
     * avatarUrl: String of user avatar url.
     */
    fun queryUsersNotInAccountGroup(
        accountGroupId: String,
        keyword: String,
        pageSize: Int,
        pageIndex: Int,
    ): List<Map<String, Any>> {
        // We have to exclude these users:
        // - The owner of this account group.
        // - The user has this account group as his/her current account group.
        // - The user has this account group as NOT his/her current account group.

        // Queries all users which belong to this account group.
        val excludedUserIds = dslContext.select(ACCOUNT_GROUP_USER.USER_ID)
            .from(ACCOUNT_GROUP_USER)
            .where(ACCOUNT_GROUP_USER.ACCOUNT_GROUP_ID.eq(accountGroupId))

        return dslContext
            .select(
                USER.ID.`as`("id"),
                USER.USERNAME.`as`("username"),
                USER.FULL_NAME.`as`("fullName"),
                USER.AVATAR_FILENAME.`as`("avatarFilename"),
            )
            .from(ACCOUNT_GROUP_USER)
            .join(USER).on(ACCOUNT_GROUP_USER.USER_ID.eq(USER.ID))
            .where(
                USER.USERNAME.like("$keyword%")
                    .and(ACCOUNT_GROUP_USER.IS_ACCOUNT_GROUP_CREATOR.isTrue())
                    .and(ACCOUNT_GROUP_USER.USER_ID.notIn(excludedUserIds))
            )
            .limit(pageSize)
            .offset(pageIndex * pageSize)
            .fetch()
            .map { p ->
                val item = p.intoMap()
                item["username"] = maskUsername(item["username"] as String)
                item["avatarUrl"] = getAvatarUrl(item["avatarFilename"] as String?)

                item
            }
    }

    /**
     * Queries account group users by account group id.
     *
     * @param accountGroupId: The string of the user account group id.
     * @param pageSize:       How many records are returned in one trip.
     * @param pageIndex:      Where to take rows.
     * @return The list of maps, each containing the following keys:
     * id: String of user id.
     * username: String of username.
     * fullName: String of user full name.
     * avatarUrl: String of user avatar url.
     * enabled: Bool indicates if the user is enabled or disabled.
     * isAccountGroupCreator: Bool indicates if the user is account group registerer.
     * isAccountGroupAdmin: Bool indicates if the user is account group admin.
     */
    fun queryAccountGroupUsers(accountGroupId: String, pageSize: Int, pageIndex: Int): List<Map<String, Any>> {
        return dslContext
            .select(
                USER.ID.`as`("id"),
                USER.USERNAME.`as`("username"),
                USER.FULL_NAME.`as`("fullName"),
                USER.AVATAR_FILENAME.`as`("avatarFilename"),
                USER.ENABLED.`as`("enabled"),
                ACCOUNT_GROUP_USER.IS_ACCOUNT_GROUP_CREATOR.`as`("isAccountGroupCreator"),
            )
            .from(ACCOUNT_GROUP_USER)
            .join(USER).on(ACCOUNT_GROUP_USER.USER_ID.eq(USER.ID))
            .where(ACCOUNT_GROUP_USER.ACCOUNT_GROUP_ID.eq(accountGroupId))
            .limit(pageSize)
            .offset(pageIndex * pageSize)
            .fetch()
            .map { p ->
                val item = p.intoMap()
                item["avatarUrl"] = getAvatarUrl(item["avatarFilename"] as String?)

                item["isAccountGroupAdmin"] = userRoleService.hasRole(
                    accountGroupId,
                    p.get("id").toString(),
                    ROLE_ACCOUNT_GROUP_ADMIN
                )

                item
            }
    }

    /**
     * Restores this user current account group to his/her own one.
     *
     * @param userId: String of user id.
     */
    fun restoreAccountGroupByUserId(userId: String) {
        val defaultAccountGroupId = dslContext.select(ACCOUNT_GROUP_USER.ACCOUNT_GROUP_ID)
            .from(ACCOUNT_GROUP_USER)
            .where(
                ACCOUNT_GROUP_USER.USER_ID.eq(userId)
                    .and(ACCOUNT_GROUP_USER.IS_ACCOUNT_GROUP_CREATOR.isTrue())
            )

        dslContext.update(USER)
            .set(USER.ACCOUNT_GROUP_ID, defaultAccountGroupId)
            .where(USER.ID.eq(userId))
            .execute()
    }

    /**
     * Account group with the input id is about to be deleted.
     * All users with this account group id as current one will be restored to their own account group id.
     *
     * @param accountGroupId: String of account group id which is about to be deleted.
     */
    fun restoreAccountGroupByAccountGroupId(accountGroupId: String) {
        dslContext.update(USER)
            .set(
                USER.ACCOUNT_GROUP_ID,
                // Subquery to select the account group ID
                dslContext.select(ACCOUNT_GROUP_USER.ACCOUNT_GROUP_ID)
                    .from(ACCOUNT_GROUP_USER)
                    .where(
                        ACCOUNT_GROUP_USER.USER_ID.eq(USER.ID)  // Reference to outer USER table
                            .and(ACCOUNT_GROUP_USER.IS_ACCOUNT_GROUP_CREATOR.isTrue())
                    )
                    .limit(1)
            )  // Ensures only one account group ID is selected per user
            .where(USER.ACCOUNT_GROUP_ID.eq(accountGroupId))  // Update condition for users in the provided account group
            .execute()
    }

    /**
     * Updates user info based on provided map data and username.
     * The map keys must match user property names.
     *
     * @param username: The string of the username whose data will be updated.
     * @param data:     The data class which contains keys with same name as user properties.
     */
    fun updateCurrentUser(username: String, @Valid data: UpdateUserDto) {
        dslContext.update(USER)
            .set(USER.FULL_NAME, data.fullName)
            .set(USER.DESCRIPTION, data.description)
            .where(USER.USERNAME.eq(username))
            .execute()
    }

    /**
     * Updates user-related roles.
     *
     * @param accountGroupId: The string of current user account group id.
     * @param data:           The map which contains keys with same name as user properties.
     */
    fun updateRoles(accountGroupId: String, @Valid data: UpdateUserRolesDto) {
        val userId = data.id.asString()
        val username = data.username

        // Makes sure current user and parameter user belong to the same user account group.
        // Makes sure account group owner's role cannot be changed.
        if (dslContextWrapper.exists(
                ACCOUNT_GROUP_USER,
                ACCOUNT_GROUP_USER.ACCOUNT_GROUP_ID.eq(accountGroupId)
                    .and(ACCOUNT_GROUP_USER.USER_ID.eq(userId))
            ) &&
            !dslContextWrapper.exists(
                ACCOUNT_GROUP_USER,
                ACCOUNT_GROUP_USER.ACCOUNT_GROUP_ID.eq(accountGroupId)
                    .and(
                        ACCOUNT_GROUP_USER.USER_ID.eq(userId)
                            .and(ACCOUNT_GROUP_USER.IS_ACCOUNT_GROUP_CREATOR.isTrue())
                    )
            )
        ) {
            val fullRoles = data.fullRoles

            // If the user roles are set.
            if (fullRoles != null && fullRoles.isNotEmpty()) {
                // Gets the role id list from the posted data.
                val roleIds = fullRoles.map { it["id"] as String }

                // Queries user current role ids.
                val currentRoleIds = userRoleService.queryRoleIds(accountGroupId, userId)

                // Gets all new added role ids.
                val addedRoleIds = roleIds.toHashSet()
                addedRoleIds.removeAll(currentRoleIds)

                // Gets all deleted role ids.
                val deletedRoleIds = currentRoleIds.toHashSet()
                deletedRoleIds.removeAll(roleIds)

                // Adds new roles if needed.
                if (addedRoleIds.isNotEmpty()) {
                    userRoleService.add(accountGroupId, userId, addedRoleIds.toList())
                }

                // Deletes roles if needed.
                if (deletedRoleIds.isNotEmpty()) {
                    userRoleService.delete(accountGroupId, userId, deletedRoleIds.toList())
                }

                // If role has been changed we need to make user re-login by deleting active tokens.
                if (addedRoleIds.isNotEmpty() || deletedRoleIds.isNotEmpty()) {
                    userActiveTokenService.deleteByUserAndAccountGroup(username, accountGroupId)
                }
            }
        }
    }

    /**
     * Updates the password for the user with the provided username.
     *
     * @param currentUserAccountGroupId: The string of current user account group id.
     * @param inputUsername: The string of the input username.
     * @param password: The string of the password.
     */
    fun updatePassword(currentUserAccountGroupId: String, inputUsername: String, password: String) {
        // beforeUpdate method in User only triggered by save method.
        // That's why we need to encode password manually.
        dslContext.update(USER)
            .set(USER.PASSWORD, PasswordEncoderFactories.createDelegatingPasswordEncoder().encode(password))
            .where(
                USER.USERNAME.eq(inputUsername)
                    .and(USER.ACCOUNT_GROUP_ID.eq(currentUserAccountGroupId))
            )
            .execute()
    }

    /**
     * Fulfills queried user data with extra query.
     *
     * @param accountGroupId The string of the user account group id.
     * @param userDetails The details data queried from db.
     * @return The userDetails with fulfilled extra details.
     */
    private fun fulfillDetails(accountGroupId: String?, userDetails: MutableMap<String, Any>?): Map<String, Any>? {
        userDetails?.let {
            // Sets the avatar url based on its filename.
            it["avatarUrl"] = getAvatarUrl(it["avatarFilename"] as String?)

            // Checks if current account group creator.
            val updatedAccountGroupId = accountGroupId ?: it["accountGroupId"].toString()

            // Checks if current account group creator.
            it["isAccountGroupCreator"] = dslContextWrapper.exists(
                ACCOUNT_GROUP_USER,
                ACCOUNT_GROUP_USER.ACCOUNT_GROUP_ID.eq(updatedAccountGroupId)
                    .and(ACCOUNT_GROUP_USER.USER_ID.eq(it["id"].toString()))
                    .and(ACCOUNT_GROUP_USER.IS_ACCOUNT_GROUP_CREATOR.isTrue())
            )

            // Queries user role titles.
            it["fullRoles"] = userRoleService.queryFullRoles(it["accountGroupId"].toString(), it["id"].toString())
        }

        return userDetails
    }
}